package tripx.sql;

import java.util.HashMap;

import tripx.util.StringHelper;

/**
 * for convenience, this handler do not support query method, it only support
 * add, delete and modify methods
 * 
 * @author zhb
 * 
 */
public class UpdateSqlHandler {

	private int status;

	private String tableName;

	public UpdateSqlHandler(String tableName) {
		this.tableName = tableName;
	}

	/**
	 * get the sql to tell how to add the target into database. this operation
	 * will always set the status to be 1.
	 * 
	 * @param target
	 */
	public String getSqlAdd(QueryObject target) {
		StringBuilder sql = new StringBuilder("INSERT INTO " + tableName + " (");
		String[] keyArray = target.getFields();
		for (String key : keyArray) {
			sql.append(key + ", ");
		}
		sql.delete(sql.length() - 2, sql.length());
		sql.append(") VALUES(");
		for (String key : keyArray) {
			if (target.getFieldClass(key).equals("int")) {
				sql.append(StringHelper.strip(target.getFieldValue(key, "0"))
						+ ", ");
			} else {
				sql.append("\""
						+ StringHelper.strip(target.getFieldValue(key, ""))
						+ "\", ");
			}
		}
		sql.delete(sql.length() - 2, sql.length());
		sql.append(")");
		status = 1;
		return sql.toString();
	}

	/**
	 * get the sql to tell how to delete the specified item. only one dimension
	 * is needed. the status will always be 1
	 * 
	 * @param key
	 *            specify which column
	 * @param value
	 *            specify the value on this column
	 */
	public String getSqlDelete(String key, String value) {
		status = 1;
		return "DELETE FROM " + tableName + " WHERE " + key + "="
				+ StringHelper.strip(value);
	}

	/**
	 * get the sql to tell how to delete the specified item. more than one
	 * dimension is supported, the status will always be 1
	 * 
	 * @param kvMap
	 * @return
	 */
	public String getSqlDelete(HashMap<String, String> kvMap) {
		StringBuilder sqlBuilder = new StringBuilder("DELETE FROM " + tableName
				+ " WHERE ");
		for (String key : kvMap.keySet()) {
			String value = StringHelper.strip(kvMap.get(key));
			// trick parse begins
			try {
				Double.parseDouble(value);
				sqlBuilder.append(key + "=" + value + " AND ");
			} catch (NumberFormatException e) {
				sqlBuilder.append(key + "=\"" + value + "\" AND ");
			}
			// trick parse ends
		}
		sqlBuilder.delete(sqlBuilder.length() - 5, sqlBuilder.length());
		status = 1;
		return sqlBuilder.toString();
	}

	/**
	 * get the sql about how to modify a record
	 * 
	 * @param key
	 *            specify which column
	 * @param oldValue
	 *            the old value
	 * @param newValue
	 *            the new value
	 */
	public String getSqlModify(String key, String newValue,
			HashMap<String, String> conditionMap) {
		StringBuilder sql = new StringBuilder("UPDATE " + tableName + " SET "
				+ key + "=");
		try {
			Double.parseDouble(newValue);
			sql.append(newValue + " WHERE ");
		} catch (NumberFormatException e) {
			sql.append("\"" + StringHelper.strip(newValue) + "\" WHERE ");
		}
		for (String condition : conditionMap.keySet()) {
			String value = StringHelper.strip(conditionMap.get(condition));
			// trick parse begins
			try {
				Double.parseDouble(value);
				sql.append(condition + "=" + conditionMap.get(condition)
						+ " AND ");
			} catch (NumberFormatException e) {
				sql.append(condition + "=\"" + conditionMap.get(condition)
						+ "\" AND ");
			}
			// trick parse ends
		}
		// remove the last `AND`
		sql.delete(sql.length() - 4, sql.length());
		status = 1;
		return sql.toString();
	}

	/**
	 * modify more than one attribute
	 * 
	 * @param kvMap
	 *            the new key-value pair
	 * @param conditionMap
	 *            condition map to specify the row
	 * @return
	 */
	public String getSqlModify(HashMap<String, String> kvMap,
			HashMap<String, String> conditionMap) {
		StringBuilder sql = new StringBuilder("UPDATE " + tableName + " SET ");
		for (String key : kvMap.keySet()) {
			String value = StringHelper.strip(kvMap.get(key));
			try {
				Double.parseDouble(value);
				sql.append(key + "=" + value + ", ");
			} catch (NumberFormatException e) {
				sql.append(key + "=\"" + value + "\", ");
			}
		}
		sql.delete(sql.lastIndexOf(", "), sql.length());
		sql.append(" WHERE ");
		for (String condition : conditionMap.keySet()) {
			String value = StringHelper.strip(conditionMap.get(condition));
			// trick parse begins
			try {
				Double.parseDouble(value);
				sql.append(condition + "=" + conditionMap.get(condition)
						+ " AND ");
			} catch (NumberFormatException e) {
				sql.append(condition + "=\"" + conditionMap.get(condition)
						+ "\" AND ");
			}
			// trick parse ends
		}
		// remove the last `AND`
		sql.delete(sql.length() - 4, sql.length());
		status = 1;
		return sql.toString();
	}

	public int getStatus() {
		return status;
	}
}
